VWG Parts Price file Import

Introduction

The VWG Parts Price import loads VWG part prices into Cloud SQL so the VWG enquiry description flow can attach prices to enquiry part lines.

Applicable Manufacturers

• VWG (VWG group)

Cloud SQL table

• Database: Cloud SQL for PostgreSQL
• Table: vwg_parts_price

Fields (Cloud SQL columns)

Column	Description	Datatype	Notes
record_identifier	Record identifier from the VWG file	VARCHAR(1)	Stored as-is
part_number	Part code/number	VARCHAR(20)	Indexed for lookups
part_price	Part price	VARCHAR(20)	Parsed to number at read time; non-numeric becomes null
local_currency	Currency code	VARCHAR(7)	Stored as-is

File format

• Delivery note: VWG may provide a zipped file. Extract it and upload the extracted .txt.
• Expected input file format: .txt

Fixed-width layout (data lines that start with M)

Only lines that start with M are treated as data lines. Fields are extracted using fixed-width character offsets (JavaScript slice(start, end), where end is exclusive), then trim() is applied. recordIdentifier and localCurrency are also converted to uppercase.

Field	Slice offsets (0-based)	Length	Validation	Notes
recordIdentifier	0..1	1	Must be M	Must be M
partNumber	7..25	18	Required; allowed character pattern (specialCharacters); max length 18	Trimmed
price	530..546	16	Required; decimal numeric pattern (decimalNumber)	Trimmed; parsed with parseFloat
localCurrency	562..567	5	Required; must be GBP	Must be GBP

Bucket matching rules

The importer accepts a filename that matches either:

• The request payload importFileName, or
• parts_price_input.txt (default allowed filename)

Importing a file

Preparation

1. Upload the extracted .txt into bucketName/importFilesDropPath.

Validation

Trigger a validation-only run by calling the importer endpoint

• POST /manufacturer/parts-file-import

Payload example:

{
  "bucketName": "fnp-imports",
  "importFilesDropPath": "manufacturer/VWG/",
  "appDirectory": "importFiles/",
  "persist": "false",
  "fnpProvider": "VWG",
  "importFileName": "parts_price_input.txt"
}

Key	Value
bucketName	Google Bucket name
importFilesDropPath	Import file drop location
appDirectory	App Directory
persist	false
fnpProvider	FNP manufacturer provider
importFileName	Import file name

Validation behaviour

Validation reads the input file line-by-line and only considers data lines that start with M.

Each considered line is extracted using fixed-width columns and validated:

• recordIdentifier must be M
• partNumber:
  • required
  • must match the allowed character pattern (specialCharacters)
  • max length is enforced (maximum 18 in validation)
• price:
  • required
  • must match the decimal numeric pattern (decimalNumber)
• localCurrency:
  • required
  • must be exactly GBP

Lines that are empty or do not start with M are ignored (they do not count as “skipped”).

At the end of validation:

• The importer uploads Report.log to the archived bucket logs area.
• It does not persist anything to Cloud SQL (because persist=false).

Importing

Trigger import by calling the importer endpoint

• POST /manufacturer/parts-file-import

Payload example:

{
  "bucketName": "fnp-imports",
  "importFilesDropPath": "manufacturer/VWG/",
  "appDirectory": "importFiles/",
  "persist": "true",
  "fnpProvider": "VWG",
  "importFileName": "parts_price_input.txt"
}

Key	Value
bucketName	Google Bucket name
importFilesDropPath	Import file drop location
appDirectory	App Directory
persist	true
fnpProvider	FNP manufacturer provider
importFileName	Import file name

Import behaviour (implementation)

• Bucket input is streamed (no download-to-disk) from GCS.
• The table is created/ensured before processing.
• In persist mode, the importer bulk loads into vwg_parts_price, moves the file to archive, and then uploads logs/notifications.

Persist-mode import conditions (what gets skipped while loading)

In persist mode (persist="true"), the importer uses streaming + PostgreSQL COPY FROM STDIN:

• It truncates the entire vwg_parts_price table before loading.
• It converts each input line into a CSV row during streaming.

During this conversion, lineToCsvRow applies the following rules:

• If the line is empty or does not start with M, the line is ignored (not counted as “skipped”).
• Otherwise, the importer extracts:
  • recordIdentifier, partNumber, price, localCurrency using the fixed-width offsets described above
  • If partNumber is missing or localCurrency is not exactly GBP, the line is treated as invalid and skipped during the load.

Important implication:

• Rows with invalid price formats can still be inserted into Cloud SQL.
• Enquiry-time parsing treats non-numeric part_price as null (so you will see price=null in enquiry output even if persist succeeded).

Testing

1. Confirm Cloud SQL table vwg_parts_price has rows.
2. Confirm part prices appear in VWG enquiry descriptions (where applicable).